package com.kila.test;

import com.kila.entity.*;
import com.kila.mapper.StudentMapper;
import com.kila.mapper.StudentcardMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.Reader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class test {

    //带转换器的增加学生

    public static void addStudentWithConverter(){
        Student stu = new Student();
        stu.setStuNo(3);
        stu.setStuName("王五");
        stu.setStuAge(17);
        stu.setGraName("3band");
        stu.setSex(true);

        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();
/**
 *

 session.insert("com.kila.entity.studentMapper."+"addStudent",stu);
 //新增加数据需要提交事务
 session.commit();
 */

            StudentMapper studentMapper  =  session.getMapper(StudentMapper.class);
             studentMapper.addStudentWithConverter(stu);
            session.commit();

            System.out.println("增加了数据");



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }


    }






  //使用了转换器查询学生
    public static void queryStudentByStunnoWithConverter(){

        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();
/**
 *
 Student s =(Student) session.selectOne("com.kila.entity.studentMapper.queryStudentByStuno",1);
 */
            StudentMapper  studentMapper =  session.getMapper(StudentMapper.class);
            Student s = studentMapper.queryStudnetByStunoWithConverter(2);
            System.out.println(s);



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }
    }









    //查询单个学生

    public static void queryStudent(){

        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();
/**
 *
            Student s =(Student) session.selectOne("com.kila.entity.studentMapper.queryStudentByStuno",1);
*/
            StudentMapper  studentMapper =  session.getMapper(StudentMapper.class);
            Student s = studentMapper.queryStudentByStuno(2);
            System.out.println(s);



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }
    }

    //查询全部学生
    public static void queryAllStudents(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();
          /**
            List<Student> students = session.selectList("com.kila.entity.studentMapper.queryAllStudents");
          */

          StudentMapper stumap =session.getMapper(StudentMapper.class);
          List<Student> students =    stumap.queryAllStudents();

          System.out.println(students);



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }

   //增加一个学生
     public static void addStudent(){
        Student stu = new Student();
        stu.setStuNo(3);
        stu.setStuName("王五");
        stu.setStuAge(17);
        stu.setGraName("3band");

        SqlSession session=null;

         try {
             //加载MyBatis连接配置文件
             Reader reader= Resources.getResourceAsReader("config.xml");
             //通过SqlSessionFactoryBuilder.build()方法获得对象
             SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

             //获取session
             session = sessionFactory.openSession();
/**
 *

             session.insert("com.kila.entity.studentMapper."+"addStudent",stu);
            //新增加数据需要提交事务
             session.commit();
 */

             StudentMapper studentMapper  =  session.getMapper(StudentMapper.class);
             int count =  studentMapper.addStudent(stu);
             session.commit();

             System.out.println("增加了"+count+"数据");



         }catch (Exception e){
             e.printStackTrace();
         }finally {
             session.close();

         }


     }


     //删除学生

    public static void deleteStudentByStuno(){


        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();
/**
 *

           int count = session.delete("com.kila.entity.studentMapper."+"deleteStudentBystuno",3);
 */

            StudentMapper studentMapper = session.getMapper(StudentMapper.class);
           int count = studentMapper.deleteStudentBystuno(3);
            //新增加数据需要提交事务
            session.commit();
            System.out.println("删除了"+count+"行数据");



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }


    }

     //修改学生
    public static void updateStudentByStuno(){


        SqlSession session=null;
        Student stu  = new Student(2,"zl",19,"g3");

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();
/**
 *

            int count = session.update("com.kila.entity.studentMapper."+"updateStudentByno",stu);

 */

             StudentMapper studentMapper = session.getMapper(StudentMapper.class);
            int count = studentMapper.updateStudentByno(stu);
            //新增加数据需要提交事务
            session.commit();
            System.out.println("修改了"+count+"行数据");



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }


    /**
     * 根据地址查找学生(使用学生对象查找)
     */
    public static void queryStudentByAddress(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();
            /**
             List<Student> students = session.selectList("com.kila.entity.studentMapper.queryAllStudents");
             */
            Address address = new Address();
            address.setHomeaddress("天河");
            address.setSchooladdress("天河");
            Student s = new Student();
            s.setAddress(address);
            StudentMapper stumap =session.getMapper(StudentMapper.class);
            List<Student> students =    stumap.queryStudentByAddress(s);

            System.out.println(students);



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }

    /**
     * 根据地址查找学生（根据地址对象查找）
     */

    public static void queryStudentByAddressWithAddress(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();
            /**
             List<Student> students = session.selectList("com.kila.entity.studentMapper.queryAllStudents");
             */
            Address address = new Address();
            address.setHomeaddress("天河");
            address.setSchooladdress("天河");
            Student s = new Student();
            s.setAddress(address);
            StudentMapper stumap =session.getMapper(StudentMapper.class);
            List<Student> students =    stumap.queryStudentByAddressWithAddress(address);

            System.out.println(students);



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }

    /**
     * 根据地址查找学生（根据传入Map对象来查找）
     */


    public static void queryStudentByAddressWithMap(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();
            /**
             List<Student> students = session.selectList("com.kila.entity.studentMapper.queryAllStudents");
             */
            HashMap<String,Object> hs = new HashMap<>();

            hs.put("schooladdress","越秀");
            hs.put("homeaddress","海珠");


            StudentMapper stumap =session.getMapper(StudentMapper.class);
            List<Student> students =    stumap.queryStudentByAddressWithMap(hs);

            System.out.println(students);



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }

    /**
     *调用存储过程根据传入的班级，获取所在班级的人数
     */


    public static void queryGranameByCount(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();
            /**
             List<Student> students = session.selectList("com.kila.entity.studentMapper.queryAllStudents");
             */
            HashMap<String,Object> hs = new HashMap<>();
            hs.put("gName","g1");


            StudentMapper stumap =session.getMapper(StudentMapper.class);

            stumap.queryCountStuByGraWithPro(hs);
             int s = (int)hs.get("countstu");

            System.out.println(s);



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }

    /**
     * 根据学号删除学生 ，使用存储过程删除
     */


    public static void delstuBynum(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();

            HashMap<String,Object> hs = new HashMap<>();
            hs.put("num",1);


            StudentMapper stumap =session.getMapper(StudentMapper.class);

            stumap.deletestuBynum(hs);
            session.commit();

            System.out.println("删除成功");



        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }

    /**
     *
     *  使用HashMap输入输出的查询方法
     */

    public static void getsStudentByNoWithHashMap(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();



            StudentMapper stumap =session.getMapper(StudentMapper.class);


            HashMap<String,Object> hs = new HashMap<>();
            hs.put("graname","g3");

            List<Map<String,Student>> stulist = stumap.getsStudentByNoWithHashMap(hs);

            System.out.println(stulist);





        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }


    /**
     *
     *  使用SQL标签进行查询
     */

    public static void queryStuByNaOrAgeWishSQLTag(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();



            StudentMapper stumap =session.getMapper(StudentMapper.class);

            Student student = new Student();

            student.setStuName("zs");


            List<Student> list =stumap.queryStuByNaOrAgeWishSQLTag(student);

            System.out.println(list);





        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }

    /**
     *
     *  使用SQL标签进行查询 使用WHERE标签进行拼接使用
     */

    public static void queryStuByNaOrAgeWishWhereTag(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();



            StudentMapper stumap =session.getMapper(StudentMapper.class);

            Student student = new Student();

            student.setStuName("zs");


            List<Student> list =stumap.queryStuByNaOrAgeWishWhereTag(student);

            System.out.println(list);





        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }



    /**
     *
     *  使用SQL标签进行查询 使用Foreach标签进行拼接使用 (迭代对象的属性集合)
     */

    public static void queryStudentWithNosInGrade(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();



            StudentMapper stumap =session.getMapper(StudentMapper.class);


            List<Integer> stuNos = new ArrayList<>();
            stuNos.add(1);
            stuNos.add(2);
            stuNos.add(6);

            Grade grade = new Grade();
            grade.setStuNos(stuNos);

            List<Student> list =stumap.queryStudentWithNosInGrade(grade);

            System.out.println(list);





        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }


    /**
     *
     *  使用SQL标签进行查询 使用Foreach标签进行拼接使用 (数组的迭代)
     */

    public static void queryStudentWithNosInArray(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();



            StudentMapper stumap =session.getMapper(StudentMapper.class);



            int[] arr = {1,2,5};

            List<Student> list =stumap.queryStudentWithNosInArray(arr);

            System.out.println(list);





        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }

    /**
     *
     *  使用SQL标签进行查询 使用Foreach标签进行拼接使用 (集合的迭代)
     */

    public static void queryStudentWithNosInList(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();



            StudentMapper stumap =session.getMapper(StudentMapper.class);

            List<Integer> list = new ArrayList<>();
            list.add(1);
            list.add(6);
            list.add(7);



//        List<Student> stulist =stumap.queryStudentWithNosInList(list);

//            System.out.println(stulist);





        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }

    /**
     *
     *  使用SQL标签进行查询 使用Foreach标签进行拼接使用 (对象集合的迭代)
     */

    public static void queryStudentWithNosInObject(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();



            StudentMapper stumap =session.getMapper(StudentMapper.class);

           Student s1= new Student();
           s1.setStuNo(1);
            Student s2= new Student();
            s2.setStuNo(3);
            Student s3= new Student();
            s3.setStuNo(2);


            Student[] students = {s1,s2,s3};


            List<Student> stulist =stumap.queryStudentWithNosInObject(students);

            System.out.println(stulist);





        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }



    /**
     *
     *  一对一 业务扩展表查询
     */

    public static void queryStuByidviewstuandcard(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();


            //连接mappper.class
            StudentMapper stumap =session.getMapper(StudentMapper.class);



         //   List<Student> stulist =stumap.queryStuByidviewstuandcard(1);

            Student s =  stumap.queryStuByidviewstuandcard(2);

            System.out.println(s);


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }


    /**
     *
     *  一对多 扩展表查询
     */

    public static void queryByClassViewStudent(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();


            //连接mappper.class
            StudentMapper stumap =session.getMapper(StudentMapper.class);



            //   List<Student> stulist =stumap.queryStuByidviewstuandcard(1);

               StudentClass stuclass =  stumap.queryByClassViewStudent(1);

               int grandID = stuclass.getClassId();
               String grandName=stuclass.getClassName();
               List<Student> stulist =stuclass.getStudents();


                System.out.println("查询到的结果数量:"+stulist.size());
                System.out.println(stulist);
               for (Student stu:stulist)  {
                   System.out.println("grandID:"+grandID+"-grandName:"+grandName);
                   System.out.println("stuno:"+stu.getStuNo()+"-stuname"+stu.getStuName()+"-stuage:"+stu.getStuAge()+"-stucardID:"+stu.getcard().getCardId()+
                           "-stucardInfo:"+stu.getcard().getCardInfo()+"-家庭住址:"+stu.getAddress().getHomeaddress()+"-学校地址:"+stu.getAddress().getSchooladdress());
            }




        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }


    /**
     *
     *  一对多 延迟查询
     */

    public static void querystucardbyidviewlazy(){
        SqlSession session=null;

        try {
            //加载MyBatis连接配置文件
            Reader reader= Resources.getResourceAsReader("config.xml");
            //通过SqlSessionFactoryBuilder.build()方法获得对象
            SqlSessionFactory sessionFactory =  new SqlSessionFactoryBuilder().build(reader);

            //获取session
            session = sessionFactory.openSession();


            //连接mappper.class
            StudentMapper stumap =session.getMapper(StudentMapper.class);



            //   List<Student> stulist =stumap.queryStuByidviewstuandcard(1);

              List<Student> sc = stumap.querystuByall();

            for (Student s:sc) {
                System.out.println(s.getStuNo()+"-"+s.getStuName()+"-"+s.getStuAge());
            }
            for (Student s:sc
            ) {
                System.out.println(s.getcard().getCardId()+"-"+s.getcard().getCardInfo());
            }






        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();

        }



    }


    public static void main(String[] args){
       //  addStudent();
       // queryAllStudent();
        // deleteStudentByStuno();
        // queryAllStudent();
       //  updateStudentByStuno();
         //queryAllStudent();
        //queryStudent();
//        addStudent();
//        queryAllStudents();
//        deleteStudentByStuno();
//        queryAllStudents();
//        updateStudentByStuno();
//        queryAllStudents();

//          使用转换器查询学生

       // addStudentWithConverter();
     //   queryStudentByStunnoWithConverter();
       // queryAllStudents();

       // queryStudentByAddress();
        //queryStudentByAddressWithAddress();
        //使用Map赋值给Mapper.xml
        //queryStudentByAddressWithMap();

        //调用个存储过程
        //queryGranameByCount();

        //调用存储过程删除根据学号删除学生
     //    delstuBynum();

        //通过hashmap赋值并返回hashmap结果
       // getsStudentByNoWithHashMap();


      //通过SQL标签进行查询处理
       // queryStuByNaOrAgeWishSQLTag();

        //通过SQL标签进行查询处理 内嵌有WHERE标签
//         queryStuByNaOrAgeWishWhereTag();

         //通过SQL标签进行查询处理，内嵌有foreach,迭代对象内的集合属性
       // queryStudentWithNosInGrade();

        //通过SQL标签进行查询处理,对数组进行迭代
//        queryStudentWithNosInArray();

        //通过SQL标签进行查询处理,对集合进行迭代
//        queryStudentWithNosInList();

        //通过SQL标签进行查询处理，对对象数组的属性进行迭代
//        queryStudentWithNosInObject();


//        通过查询一对一扩展业务类进行查询
//        queryStuByidviewstuandcard();

        //通过一对多扩展业务类进行查询
//        queryByClassViewStudent();

         //通过一对多查询 延迟查询

        querystucardbyidviewlazy();


    }
}
